package com.gxuwz.gj.action;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.gxuwz.javabean.Basedata;
import com.gxuwz.javabean.WLBM_XSZ;

public class MainProcess {
	public static void main(String[] args) {
		//读取数据库,先获取 物料编码和销售组描述
		// 得到这些之后,再生成相同 物料编码和销售组描述 相同的数据的累加
		try {
//			String sql = "select WLBM, XSZMS from basedata";
			String sql = "select * from basedata";
			
			//  得到所有的数据,把相同物料编码的
			
			ResultSet rs = MainProcess.getResultSet(sql);
			Set<WLBM_XSZ> wlbm_xsz_list = new HashSet<WLBM_XSZ>();
			if (null != rs) {
				while (rs.next()) {
	//					System.out.println(rs.getRow());
					WLBM_XSZ wx = new WLBM_XSZ();
					wx.setWlbm(rs.getString("WLBM"));
					wx.setXsz(rs.getString("XSZMS"));
					wlbm_xsz_list.add(wx);  
				}
			}
//				System.out.println(wlbm_xsz_list.size());
			// 去得所有的数据
			String sql1 = "select * from basedata";
			ResultSet rs1 = MainProcess.getResultSet(sql1);
			List<Basedata> bds = initList4Database(rs1);
			Connection con = MainProcess.getCon();
			//先累加到另一张表
			if (null != rs) {
				while (rs.next()) {  //F0500100001 广西
					float chje = 0f;
					for (Basedata db : bds) {  //计算累加
						if ((rs.getString("WLBM").equals(db.getWLBM()))
								&& rs.getString("XSZMS").equals(db.getWLBM())) {
							// 首先获取 WLBM 为F0500100001,XSZMS为广西的数据
							// 净价值 和 折扣 相减 作为一个新的字段 出货金额(CHJE)
							chje += (db.getJJZ() - db.getZK());
						}
					}  // while rs1.next()
					
					// 把数据保存到另一张表里
//					String sql_insert = "insert into basedata_temp(KPPZ, PPLX, "  // 1
//							+ "CJFPRQ, FKF, FKFMS, "  // 2
//							+ "SDF, SDFMS, JHD, "  // 3
//							+ "XSDD, XSJG, XSBM, "  // 4
//							+ "XSBMMS, XSZ, XSZMS, "  // 5
//							+ "FZYG, XM, WLBM, "  // 6
//							+ "WLMS, WLZ1MS, WLZ2MS, "  // 7
//							+ "KPSL, XSDW, JJZ, "  // 8
//							+ "SE, CB, ZK, "  // 9
//							+ "CHJE ) "  // 10
//							+ " VALUES('"+rs1.getString("KPPZ")+"', '"+rs1.getString("PPLX")+"',"  // 1
//									+ "'"+rs1.getDate("CJFPRQ")+"', '"+rs1.getString("FKF")+"', '"+rs1.getString("FKFMS")+"', "  // 2
//									+ "'"+rs1.getString("SDF")+"', '"+rs1.getString("SDFMS")+"', '"+rs1.getString("JHD")+"',"  // 3
//									+ "'"+rs1.getString("XSDD")+"', '"+rs1.getString("XSJG")+"', '"+rs1.getString("XSBM")+"',"  // 4
//									+ "'"+rs1.getString("XSBMMS")+"', '"+rs1.getString("XSZ")+"', '"+rs1.getString("XSZMS")+"',"  // 5
//									+ "'"+rs1.getString("FZYG")+"', '"+rs1.getString("XM")+"', '"+rs1.getString("WLBM")+"',"  // 6
//									+ "'"+doFlag(rs1.getString("WLMS"))+"', '"+rs1.getString("WLZ1MS")+"', '"+rs1.getString("WLZ2MS")+"',"  // 6
//									+ ""+rs1.getFloat("KPSL")+", '"+rs1.getString("XSDW")+"', "+rs1.getFloat("JJZ")+""  // 7
//									+ ","+rs1.getFloat("SE")+", "+rs1.getFloat("CB")+", "+rs1.getFloat("ZK")+", "  // 8
//									+ ""+chje+""  // 9
//								+ ")";
//					System.out.println(sql_insert);
//					PreparedStatement pstat = con.prepareStatement(sql_insert);
//					pstat.executeUpdate();
				}  //while (rs.next()) {
			}  // if
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
	}

	private static List<Basedata> initList4Database(ResultSet rs1)
			throws SQLException {
		List<Basedata> bds = new ArrayList<Basedata>();
		
		while (rs1.next()) {  //计算累加
			Basedata bd = new Basedata();
			bd.setCB(rs1.getFloat("CB"));
			bd.setCHJE(rs1.getFloat("CHJE"));
			bd.setCJFPRQ(rs1.getDate("CJFPRQ"));
			bd.setFKF(rs1.getString("FKF"));
			bd.setFKFMS(rs1.getString("FKFMS"));
			bd.setFZYG(rs1.getString("FZYG"));
			bd.setJHD(rs1.getString("JHD"));
			bd.setJJZ(rs1.getFloat("JJZ"));
			bd.setKPPZ(rs1.getString("KPPZ"));
			bd.setKPSL(rs1.getFloat("KPSL"));
			bd.setPPLX(rs1.getString("PPLX"));
			bd.setSDF(rs1.getString("SDF"));
			bd.setSDFMS(rs1.getString("SDFMS"));
			bd.setSE(rs1.getFloat("SE"));
			bd.setWLBM(rs1.getString("WLBM"));
			bd.setWLMS(rs1.getString("WLMS"));
			bd.setWLZ1MS(rs1.getString("WLZ1MS"));
			bd.setWLZ2MS(rs1.getString("WLZ2MS"));
			bd.setXM(rs1.getString("XM"));
			bd.setXSBM(rs1.getString("XSBM"));
			bd.setXSBMMS(rs1.getString("XSBMMS"));
			bd.setXSDD(rs1.getString("XSDD"));
			bd.setXSDW(rs1.getString("XSDW"));
			bd.setXSJG(rs1.getString("XSJG"));
			bd.setXSZ(rs1.getString("XSZ"));
			bd.setXSZMS(rs1.getString("XSZMS"));
			bd.setZK(rs1.getFloat("ZK"));
			
			bds.add(bd);
		}
		
		return bds;
	}
	
	/**
	 * 删除最后一个"\"
	 * @param string
	 * @return
	 */
	private static String doFlag(String str) {
		if (null != str) {
			if ("\\".equals(str.substring(str.length()-1))) {
				return str.substring(0, str.length()-2);
			}
		}
		
		return "";
	}

	public static ResultSet getResultSet(String sql) {
		ResultSet rs = null;
		Connection con = null;
		try {
			con = MainProcess.getCon();
			PreparedStatement pstat = con.prepareStatement(sql);
			rs = pstat.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			if (null != con) {
//				try {
//					con.close();
//				} catch (SQLException e) {
//					e.printStackTrace();
//				}
//			}
		}
		
		return rs;
	}
	
	public static Connection getCon() {
		String url = "jdbc:mysql://localhost:3306/tj?useUnicode=true&characterEncoding=utf-8";
		String user = "root";
		String pwd = "";
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return con;
	}
}
